package devbureau.fstore.common.db;

import devbureau.fstore.common.DeliveryData;
import devbureau.fstore.common.DeliveryDataImpl;
import devbureau.fstore.common.DeliveryInfoImpl;
import devbureau.fstore.common.Order;
import devbureau.fstore.common.OrderData;
import devbureau.fstore.common.OrderLine;
import devbureau.fstore.common.OrderStatus;
import devbureau.fstore.common.Store;
import devbureau.fstore.common.ItemData;
import devbureau.fstore.common.ItemDataImpl;
import devbureau.fstore.common.TranslatorItem;
import devbureau.fstore.common.load.FileParserFactory;
import devbureau.fstore.common.Item;
import devbureau.fstore.common.ItemWriteOffData;
import devbureau.fstore.common.OrderLineData;
import devbureau.fstore.common.Pair;
import devbureau.fstore.common.StoreItemFilter;
import devbureau.fstore.common.StoreItemOrder;
import devbureau.fstore.common.StoreItemOrderDir;
import devbureau.fstore.common.StoreItemOrderField;
import devbureau.fstore.common.load.FileParser;
import devbureau.fstore.common.report.Bill;
import devbureau.fstore.util.DBHelper;
import devbureau.fstore.util.StoreHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;

public class StoreDBImpl implements Store {

   	private static Logger logger = Logger.getLogger(StoreDBImpl.class);

    public static final String SQL_LIST_DELIVERIES = "select ID, CREATED, FILE_NAME, RATE, LOGIN, CURRENCY from STORE.DELIVERY";
	public static final String SQL_GET_DELIVERY = "SELECT ID, FILE_DATA, FILE_NAME, RATE, CURRENCY, LOGIN, CREATED FROM STORE.DELIVERY WHERE ID = ?";

    public static final String SQL_LIST_ITEMS = "select * from STORE.V_ITEM ";
    public static final String SQL_LIST_ITEMS_MODIFIED = "select max(MODIFIED) from STORE.V_ITEM";
    public static final String SQL_DELETE_ITEM = "delete from STORE.ITEM where ID = ?";

    public static final String SQL_CHANGE_ORDER_STATUS =   "UPDATE STORE.ORDER SET STATUS_ID = ? WHERE ID = ?";
    public static final String SQL_LIST_ORDERS = "select * from STORE.V_ORDER ";
    public static final String SQL_GET_ORDER = "select * from STORE.V_ORDER where ID = ?";

    public static final String SQL_WHERE_ORDERS_SELLER = " SELLER = ? AND ";
    public static final String SQL_WHERE_ORDERS_STATUS = " STATUS_ID IN ( ? ) AND ";
    public static final String SQL_WHERE_ORDERS_ID = " ID = ? AND ";
    public static final String SQL_WHERE_DATE = " CREATED > ? AND CREATED < ? AND ";
    
    public static final String SQL_LIST_LINES = "select * from STORE.V_LINE where ORDER_ID = ?";
    public static final String SQL_GET_ORDER_BY_ITEM = "SELECT ORDER_ID from STORE.ORDER_LINE where ID = ?";
    public static final String SQL_UPDATE_ORDER = "update STORE.ORDER set CUSTOMER_ID = ? where ID = ?";
    public static final String SQL_UPDATE_ORDER_STATUS = "update STORE.ORDER set STATUS_ID = ? where ID = ?";
    public static final String SQL_GET_ACTIVE_ORDER = "SELECT ID FROM STORE.ORDER WHERE SELLER = ? AND STATUS_ID = 0";

    public static final String SQL_LIST_CUSTOMERS = "select distinct(CUSTOMER) from STORE.ORDER order by CUSTOMER";
    public static final String SQL_LIST_CUSTOMERS_AJAX = "SELECT id, label FROM STORE.CUSTOMER WHERE LOWER(label) LIKE '%?%' AND is_active = 1 ORDER BY label";

    public static final String SQL_LIST_SUPPLIER = "select distinct(SUPPLIER_LABEL) from STORE.ITEM";

    public static final String SQL_BILL_REPORT = "select * from STORE.V_BILL_REPORT where created > ? and created < ?";

    public static final String SQL_TRANSLATOR = "update STORE.ITEM I set I.NAME_RU = (select RU from STORE.TRANSLATOR T where T.EN = I.NAME) where I.NAME in (select EN from STORE.TRANSLATOR)";
    public static final String SQL_LIST_TRANSLATOR_ITEMS = "select distinct(NAME),NAME_RU from STORE.ITEM order by NAME";
    public static final String SQL_INSERT_TRANSLATOR_ITEMS = "insert into STORE.TRANSLATOR(EN,RU) values (?,?)";

    public static final String SQL_BALANCE_REPORT_0 = "drop table SESSION.BALANCE";
    public static final String SQL_BALANCE_REPORT_1 = "declare global temporary table SESSION.BALANCE like STORE.V_ITEM not logged in USERTEMP1 on commit preserve rows";
    public static final String SQL_BALANCE_REPORT_2 = "insert into SESSION.BALANCE  select * from STORE.V_ITEM VI";
    public static final String SQL_BALANCE_REPORT_3 = "merge into SESSION.BALANCE B using (select L.ITEM_ID,sum (L.QUANTITY) as QUANTITY from STORE.V_LINE L join STORE.V_ORDER O on L.ORDER_ID = O.ID where O.STATUS_ID in (0,1) group by L.ITEM_ID) as OL on (B.ID = OL.ITEM_ID) when matched then update set  B.BALANCE = B.BALANCE + OL.QUANTITY";
    public static final String SQL_BALANCE_REPORT_4 = "select * from SESSION.BALANCE B where B.BALANCE > 0 order by B.NAME";

    public static final String SQL_LIST_ITEM_WRITE_OFF = "select DL.DELIVERY_ID , DL.ITEM_ID, I.NAME, I.SUPPLIER_LABEL, DL.QUANTITY, I.BALANCE, OL.ORDER_ID , sum(OL.QUANTITY) as ORDER_QUANTITY ,  I.BALANCE - sum(OL.QUANTITY) as DELTA from STORE.DELIVERY_LINE DL join STORE.V_ITEM I on DL.ITEM_ID = I.ID join STORE.ORDER_LINE OL on OL.ITEM_ID = DL.ITEM_ID where DELIVERY_ID = ? group by DL.DELIVERY_ID, DL.ITEM_ID, I.NAME, I.SUPPLIER_LABEL, DL.QUANTITY, I.BALANCE, OL.ORDER_ID having I.BALANCE - sum(OL.QUANTITY) < 0 order by DL.ITEM_ID";

    private void changeOrderStatus(Integer orderId, Integer newStatus) throws Exception{
        logger.debug("#changeOrderStatus orderId["+orderId+"] newStatus["+newStatus+"]");
        Connection connection = null;
        PreparedStatement ps =null;
        ResultSet rs  = null;

        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_CHANGE_ORDER_STATUS);
            ps.setInt(1, newStatus);
            ps.setInt(2, orderId);
			ps.executeUpdate();
            ps.close();

        }catch(Exception e){
            logger.fatal(e);
            throw new Exception("Заказ не утвержен. Произошла ошибка: "+e.getMessage());
        }finally{
            DBHelper.closeAll(connection, rs, ps);
        }
        logger.debug("#changeOrderStatus exit");
    }

	@Override
	public Integer makeOrder(Order order, String login) throws Exception {
        logger.debug("#makeOrder enter");
        Connection connection = null;
        Integer orderId = null;
        try{
            connection = DBHelper.getConnection();
            CreateOrderDBTask task = new CreateOrderDBTask(connection, order, login);
            task.run();
            orderId = task.getOrderId();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Создать заказ не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#makeOrder orderId = " + orderId);
        logger.debug("#makeOrder exit");

        return orderId;
	}

    public List<ItemData> listItems(StoreItemFilter filter,StoreItemOrder order) throws Exception {
        //provide default values
        if(filter == null){
            filter = new StoreItemFilter(Boolean.TRUE);
        }
        if(order == null){
            order = new StoreItemOrder(StoreItemOrderField.NAME, StoreItemOrderDir.asc);
        }
        logger.debug("#listItems enter");
        double stime = System.currentTimeMillis();
        List<ItemData> items = new ArrayList<ItemData>();
        Connection connection = null;

        try{
            connection = DBHelper.getConnection();
            String sql = SQL_LIST_ITEMS;
            if(filter.isBalansNotZero()!= null || filter.getSupplier()!= null) {
                sql += " where ";
            }
            
            if(filter.isBalansNotZero()!=null && filter.isBalansNotZero()) {
                sql += " BALANCE > 0 ";
            }

            if(filter.getSupplier()!=null ) {
            	if(!sql.endsWith(" where ")) {
            		sql += " and ";
            	}
                sql += " SUPPLIER_LABEL = '"+ filter.getSupplier() +"' ";
            }
            
            if(filter.getName()!=null ) {
            	if(!sql.endsWith(" where ")) {
            		sql += " and ";
            	}
                sql += " lower(NAME) like ('%"+ filter.getName().toLowerCase() +"%') ";
            }
            
           
            if(order != null) {
                sql += " order by " + order.getField().toString() + " " + order.getDir().toString();
            }

            logger.debug("#listItems sql = " + sql);
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){

                Integer id = rs.getInt("ID");
                String supplierLabel = rs.getString("SUPPLIER_LABEL");
                String plantation = rs.getString("PLANTATION");
                String name = rs.getString("NAME");
                String nameRU = rs.getString("NAME_RU");
                Integer length = rs.getInt("LENGTH");
                Date deliveryDate = null;
                if(rs.getString("INCOMING_DATE")!= null){
                    deliveryDate =  new Date(rs.getTimestamp("INCOMING_DATE").getTime());
                }
                Integer quantity = rs.getInt("QUANTITY");
                Double price = rs.getDouble("PRICE");
                Double priceRUR = rs.getDouble("PRICE_RUR");
                Integer balance = rs.getInt("BALANCE");
                String currency = rs.getString("CURRENCY");

                items.add( new ItemDataImpl(id, supplierLabel,name,nameRU,length, deliveryDate, quantity, price, plantation, currency, priceRUR, balance));

            }
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Полчить список заказов не получилось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#listItems time = " + (System.currentTimeMillis() - stime));
        logger.debug("#listItems exit");
        return items;
    }

    public List<OrderData> listOrders(String login,List<Integer> orderStatus,Date from,Date to) throws Exception {
        logger.debug("#listOrders enter");
        List<OrderData> orders = null;

        Connection connection = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String whereCond = "";
        try{
            String query = SQL_LIST_ORDERS;
            if(login!=null || (orderStatus!=null && orderStatus.size() >0)){
                whereCond += " where ";
            }
            if(login!=null){
                whereCond+= SQL_WHERE_ORDERS_SELLER.replaceAll("\\?", "'"+login+"'");
            }
            if(orderStatus!=null && orderStatus.size() >0 ){
                String str = "";
                for(Integer status : orderStatus) {
                    str += status;
                    str += ",";
                }
                str = str.substring(0,str.length()-1);
                whereCond+= SQL_WHERE_ORDERS_STATUS.replaceAll("\\?", str);
            }

            if(from!= null && to!= null) {
                whereCond+= SQL_WHERE_DATE;
            }

            if(login!=null || orderStatus!=null || from!=null || to != null){
                whereCond = whereCond.substring(0, whereCond.length()-4);
            }
            query += whereCond;
            query += " order by ID desc";
            logger.debug("listOrders(login="+login+", orderstatus="+orderStatus+") query =["+query+"]");

            ps = connection.prepareStatement(query);
            if(from!= null && to!= null) {
                ps.setTimestamp(1, new Timestamp(from.getTime()));
                ps.setTimestamp(2, new Timestamp(to.getTime()));
            }

            rs = ps.executeQuery();
            orders = StoreHelper.parseOrders(rs);
        }catch(Exception ex){
            logger.fatal("Error in listOrders", ex);
            throw new Exception("Список заказов типа ["+orderStatus+"] для пользователя "+login+" не загружен. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeAll(connection, rs, ps);
        }
        logger.debug("#listOrders exit");
        return orders;
    }

    public List<DeliveryData> listDeliveries() throws Exception {
        logger.debug("#listDeliveries enter");
        Connection connection = null;
        List<DeliveryData> deliveries = new ArrayList<DeliveryData>();
        try{

            connection = DBHelper.getConnection();
            PreparedStatement ps = connection.prepareStatement(SQL_LIST_DELIVERIES);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
               deliveries.add( new DeliveryDataImpl(rs.getInt("ID"),
                                                    rs.getString("FILE_NAME"),
                                                    rs.getDouble("RATE"),
                                                    rs.getString("CURRENCY"),
                                                    new Date(rs.getTimestamp("CREATED").getTime()),
                                                    rs.getString("LOGIN")
                                                    ));
            }
            rs.close();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("#listDeliveries Не удалось получить список поставок Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }

        logger.debug("#listItemsModified exit");
        return deliveries;
    }
    
    public Date listItemsModified() throws Exception {
        logger.debug("#listItemsModified enter");
        Date modified = new Date(0);
        Connection connection = null;
        try{

            connection = DBHelper.getConnection();
            PreparedStatement ps = connection.prepareStatement(SQL_LIST_ITEMS_MODIFIED);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getTimestamp(1) != null) {
                    modified =  new Date(rs.getTimestamp(1).getTime());
                }
            }
            rs.close();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("#listItemsModified Не удалось получить последние время обновления каталога Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }

        logger.debug("#listItemsModified exit");
        return modified;
    }
    /**Get user's order which is in configuration process. Use this method in auth-routine.
     * @param user login
     * @return OrderData or null if user doesn't have current order.
     */
    public Integer getActiveOrderId(String login){
        Integer orderId = null;

        PreparedStatement ps =null;
        ResultSet rs  = null;
        Connection con = null;
        try{
            con = DBHelper.getConnection();
            ps = con.prepareStatement(SQL_GET_ACTIVE_ORDER);
            ps.setString(1, login);
            rs = ps.executeQuery();
            if(rs.next()){
                orderId = rs.getInt("ID");
            }
        }catch(Exception e){
            logger.error("Error while getting ORDER_ID by ORDER_LINE_ID", e);
        }
        finally{
            DBHelper.closeAll(con, rs, ps);
        }
        return orderId;
    }

    public OrderData getOrder(Integer orderId) throws Exception {
        OrderData order = null;
        PreparedStatement ps =null;
        ResultSet rs  = null;
        Connection con = null;
        try{
            con = DBHelper.getConnection();
            String query = SQL_GET_ORDER;
            ps = con.prepareStatement(query);
            logger.debug("#getOrder query = " + query);
            ps.setInt(1, orderId);
            
            rs = ps.executeQuery();
            order = StoreHelper.parseOrders(rs).get(0);
            ps.close();
            rs.close();

            ps = con.prepareStatement(SQL_LIST_LINES);
            ps.setInt(1, orderId);
            rs = ps.executeQuery();
            List<OrderLineData> orderLines = StoreHelper.parseOrderLines(rs);
            order.setLines(orderLines);


        }catch(Exception e){
            logger.error("#getOrder id = " + orderId, e);
            throw new Exception("Заказ с ИД["+orderId+"] не найден. Произошла ошибка:" + e.getMessage());
        }
        finally{
            DBHelper.closeAll(con, rs, ps);
        }
        return order;
    }
    
    public DeliveryData getDelivery(Integer id) throws Exception{
        DeliveryDataImpl deliveryImpl = null;
        
        Connection connection = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_GET_DELIVERY);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if(rs.next()){
                deliveryImpl = new DeliveryDataImpl(rs.getInt("ID"), rs.getString("FILE_NAME"), rs.getDouble("RATE"), rs.getString("CURRENCY"), new java.util.Date(rs.getTimestamp("CREATED").getTime()), rs.getString("LOGIN"));
                deliveryImpl.setContent(rs.getBytes("FILE_DATA"));
            }
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeResultSet(rs);
            DBHelper.closeStatement(ps);
            DBHelper.closeConnection(connection);
        }
        return deliveryImpl;
    }
    public Integer makeOrder(Integer orderId, String login) throws Exception{
        return makeOrder(getOrder(orderId),login);
    }

    public void updateOrderLine(Integer lineId,Double priceRUR,Integer quantity) throws Exception {
        logger.debug("#updateOrderLine enter");
        Connection connection = null;
        try{
            connection = DBHelper.getConnection();
            UpdateOrderLineDBTask task = new UpdateOrderLineDBTask(connection, lineId,quantity,priceRUR);
            task.run();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#updateOrderLines exit");
    }

    public void deleteOrderLine(Integer lineId) throws Exception{
        logger.debug("#deleteOrderLine enter");
        Connection connection = null;
        try{
            connection = DBHelper.getConnection();
            DeleteOrderLineDBTask task = new DeleteOrderLineDBTask(connection, lineId);
            task.run();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#deleteOrderLine exit");
    }

    public Integer addOrderLine(Integer orderId,OrderLine line) throws Exception{
        logger.debug("#addOrderLine enter");
        Connection connection = null;
        Integer lineId = null;
        try{
            connection = DBHelper.getConnection();
            CreateOrderLineDBTask task = new CreateOrderLineDBTask(connection, orderId,line);
            task.run();
            lineId = task.getLineId();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Добавить позицию к заказу. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#addOrderLine exit lineId -> " + lineId);

        return lineId;
    }

    @Override
    public Integer importDelivery(String login, byte[] fileData,String fileName, Double rate, String currency) throws Exception{

        logger.debug("#importDelivery enter");
        logger.info("#importDelivery login = " + login);
        logger.info("#importDelivery filename = " + fileName);
        logger.info("#importDelivery rate = " + rate);
        logger.info("#importDelivery currency = " + currency);

        // parse file
		FileParser parser = FileParserFactory.getFileParser(fileName);
    	List<Item> items = parser.parse(fileData);
        DeliveryInfoImpl delivery = new DeliveryInfoImpl(fileName, rate, currency, new Date() ,login);
        delivery.setContent(fileData);

        Connection connection = null;
        LoadDeliveryDBTask task = null;

        try{
            connection = DBHelper.getConnection();
            task = new LoadDeliveryDBTask(connection, items, delivery);
            task.run();
            logger.debug("#importDelivery before LoadDeliveryItemsDBTask  task.run() FINISHED");
            translate();
            logger.debug("#importDelivery translate OK");
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Загрузить обновление не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }

        logger.debug("#importDelivery deliveryId = " + task.getDeliveryId());
        logger.debug("#importDelivery exit");
        return task.getDeliveryId();
  }

    public void deleteItem(Integer itemId) throws Exception {
        logger.debug("#deleteItem enter");
        Connection connection = null;
        try{
            connection = DBHelper.getConnection();
            PreparedStatement ps = connection.prepareStatement(SQL_DELETE_ITEM);
            ps.setInt(1, itemId);
            ps.executeUpdate();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Не удалось удалить позицию на складе " + itemId +". Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }

        logger.debug("#deleteItem exit");

    }

    public void deleteOrder(Integer orderId) throws Exception {
        logger.debug("#deleteOrder enter");
        Connection connection = null;
        try{
            connection = DBHelper.getConnection();
            DeleteOrderDBTask task = new DeleteOrderDBTask(connection, orderId);
            task.run();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#deleteOrder exit");
    }

    public Integer getOrderIdByOrderLineId(Integer orderLineId){
        Integer orderId = null;

        PreparedStatement ps =null;
        ResultSet rs  = null;
        Connection con = null;
        try{
            con = DBHelper.getConnection();
            ps = con.prepareStatement(SQL_GET_ORDER_BY_ITEM);
            ps.setInt(1, orderLineId);
            rs = ps.executeQuery();
            if(rs.next()){
                orderId = rs.getInt(1);
            }
        }catch(Exception e){
            logger.error("Error while getting ORDER_ID by ORDER_LINE_ID", e);
        }
        finally{
            DBHelper.closeAll(con, rs, ps);
        }
        return orderId;
    }

    public Integer updateOrder(Integer orderId, Integer newCustomerId) throws Exception {
        logger.debug("#updateOrder enter");
        Connection connection = null;
        PreparedStatement ps =null;

        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_UPDATE_ORDER);

            if(newCustomerId != null) {
                ps.setInt(1, newCustomerId);
            }else{
                ps.setNull(1, Types.INTEGER);
            }

            ps.setInt(2, orderId);
            ps.executeUpdate();
            ps.close();

        }catch(Exception ex){
            logger.fatal(ex);
            orderId = null;
            throw new Exception("Изменить данные о заказе не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#updateOrder exit");
        return orderId;
    }

    public void updateOrderStatus(Integer orderId, OrderStatus status) throws Exception {
        logger.debug("#updateOrderStatus enter ");
        logger.debug("ORDER ID = " + orderId);
        logger.debug("STATUS " + status);
        Connection connection = null;
        PreparedStatement ps = null;

        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_UPDATE_ORDER_STATUS);

            ps.setInt(1, status.getNum());
            ps.setInt(2, orderId);
            ps.executeUpdate();
            ps.close();
            logger.debug("STATUS ID = " + status.getNum());

        }catch(Exception ex){
            logger.fatal(ex);
            orderId = null;
            throw new Exception("Изменить данные о заказе не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#updateOrderStatus exit");
    }

    public List<String> listSuppliers(StoreItemFilter filter) throws Exception {
        logger.debug("#listSupplier enter");
        double stime = System.currentTimeMillis();
        List<String> suppliers = new ArrayList<String>();
        Connection connection = null;

        try{
            connection = DBHelper.getConnection();
            String sql = SQL_LIST_SUPPLIER;

            if(filter.isBalansNotZero()) {
                sql += " where BALANCE > 0 ";
            }
            sql += " order by SUPPLIER_LABEL";

            logger.debug("#listSuppliers sql = " + sql);
            
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                suppliers.add(rs.getString("SUPPLIER_LABEL"));
            }
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Полчить список поставщиков не получилось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#listSupplier time = " + (System.currentTimeMillis() - stime));
        logger.debug("#listSupplier exit");
        return suppliers;
    }

    public List<Bill> getBillReport(Date from, Date to) throws Exception{
        logger.debug("from["+from+"] to["+to+"]");
        List<Bill> bills = new ArrayList<Bill>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con =  DBHelper.getConnection();
            ps = con.prepareStatement(SQL_BILL_REPORT);
            ps.setTimestamp(1, new java.sql.Timestamp(from.getTime()));
            ps.setTimestamp(2, new java.sql.Timestamp(to.getTime()));
            rs = ps.executeQuery();
            while(rs.next()){
                Bill bill = new Bill(rs.getInt("ORDER_ID"), rs.getString("CUSTOMER_LABEL"), rs.getInt("total_quantity"), rs.getDate("created"),  rs.getDouble("total_price_delivery"), rs.getDouble("total_price_sell"));
                bills.add(bill);
            }
            logger.debug("bills report size["+bills.size()+"]");

        }catch(Exception e){
            logger.error("Error while getting bills report data",e);
            throw new Exception("Произошла ошибка при построении отчёта. Отчёт не создан.");
        }finally{
            DBHelper.closeAll(con, rs, ps);
        }
        return bills;
    }

    /**Used in AjaxServlet
     *@param partialLabel is an user input fragment
     * @return List of Pair: id => label with matches
     *
     */
    public List<Pair<Integer,String>> getAjaxCustomers(String partialLabel) throws Exception{
        logger.debug("getAjaxCustomers -> " + partialLabel);
        List<Pair<Integer,String>> result = new ArrayList<Pair<Integer, String>>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = DBHelper.getConnection();
            ps = con.prepareStatement(SQL_LIST_CUSTOMERS_AJAX.replace("?", partialLabel.toLowerCase()));
            rs = ps.executeQuery();
            while(rs.next()){
                result.add( new Pair<Integer, String>(rs.getInt("id"), rs.getString("label")) );
            }
            logger.debug("found" + result.size() + " rows for partial ["+partialLabel+"]");
        }catch(Exception e){
            logger.debug("Error while getting ajax customrers", e);
            throw new Exception("Произошла ошибка при поиске грузополучателей");
        }finally{
            DBHelper.closeAll(con, rs, ps);
        }
        return result;
    }

    public void deleteDelivery(Integer deliveryId) throws Exception {
        logger.info("#deleteDelivery deliveryId = " + deliveryId);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = DBHelper.getConnection();
            DeleteDeliveryDBTask task = new DeleteDeliveryDBTask(con, deliveryId);
            task.run();
        }catch(Exception e){
            logger.debug("Error while processing delete delivery", e);
            throw new Exception("Ошибка удаления инвойса. Возможно существуют заказы связанный с товарами из удаляемого инвойса." + e.getMessage());
        }finally{
            DBHelper.closeAll(con, rs, ps);
        }
    }


    public void translate() throws Exception {
        logger.debug("#translate enter");
        Connection connection = null;
        PreparedStatement ps =null;

        try{
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_TRANSLATOR);

            ps.executeUpdate();
            ps.close();

        }catch(Exception ex){
            logger.fatal("#translate",ex);
            throw new Exception("Осуществить перевод не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#translate exit");
    }

    public List<TranslatorItem> listTranslatorItems() throws Exception {
        logger.debug("#listTranslatorItems enter");
        List<TranslatorItem> items = new ArrayList();

        Connection connection = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            ps = connection.prepareStatement(SQL_LIST_TRANSLATOR_ITEMS);
            rs = ps.executeQuery();
            while(rs.next()){
                items.add(new TranslatorItem(rs.getString("NAME"),rs.getString("NAME_RU")));
            }
        }catch(Exception ex){
            logger.fatal("Error in listOrders", ex);
            throw new Exception("Не удалось загрузить словарь. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeAll(connection, rs, ps);
        }
        logger.debug("#listOrders exit");
        return items;
    }

    public void  importTranslation(byte[] fileData, String fileName) throws Exception {
        logger.debug("#importTranslation enter");

        // parse file
		devbureau.fstore.common.translate.FileParser parser = devbureau.fstore.common.translate.FileParserFactory.getFileParser(fileName);
    	List<TranslatorItem> items = parser.parse(fileData);

        Connection connection = null;

        try{
            connection = DBHelper.getConnection();
            deleteTranslatorItems(items);
            insertTranslatorItems(items);
            logger.info("#importTranslation UPDATE FINISHED");
            translate();
            logger.info("#importTranslation TRANSLATE OK");
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Загрузить обновление не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#imporimportTranslationtDelivery exit");
    }

    public void deleteTranslatorItems(List<TranslatorItem> items) throws Exception {
        logger.debug("#deleteTranslatorItems enter");
        Connection connection = null;
        PreparedStatement ps =null;

        try{
            connection = DBHelper.getConnection();
            String query = "delete from STORE.TRANSLATOR where EN in (" ;
            Iterator<TranslatorItem> iterator = items.iterator();
            while(iterator.hasNext()){
                query += "'"+iterator.next().getName()+"',";
            }
            query = query.substring(0,query.length() - 1 );
            query += ")";

            logger.debug("deleteTranslatorItems query = " + query);
            ps = connection.prepareStatement(query);

            ps.executeUpdate();
            ps.close();

        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Удалить записи не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#deleteTranslatorItems exit");
    }

    public void insertTranslatorItems(List<TranslatorItem> items) throws Exception {
        logger.debug("#deleteTranslatorItems enter");
        Connection connection = null;
        PreparedStatement ps =null;

        try{
            connection = DBHelper.getConnection();
            Iterator<TranslatorItem> iterator = items.iterator();
            while(iterator.hasNext()){
                TranslatorItem item = iterator.next();
                ps = connection.prepareStatement(SQL_INSERT_TRANSLATOR_ITEMS);
                ps.setString(1, item.getName());
                ps.setString(2, item.getNameRU());
                ps.executeUpdate();
                ps.close();
            }
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Удалить записи не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#deleteTranslatorItems exit");
    }

    public List<ItemData> getBalanceReport() throws Exception {
        logger.debug("#getBalanceReport enter");
        List<ItemData> items = new ArrayList<ItemData>();
        Connection connection = null;

        try{
            connection = DBHelper.getConnection();
            PreparedStatement ps = null;
            try{
                logger.debug("#getBalanceReport sql = " + SQL_BALANCE_REPORT_0);
                ps = connection.prepareStatement(SQL_BALANCE_REPORT_0);
                ps.executeUpdate();
            }catch(Exception exx){
                logger.warn("#getBalanceReport", exx);
            }finally{
                ps.close();
            }
            logger.debug("#getBalanceReport sql = " + SQL_BALANCE_REPORT_1);
            ps = connection.prepareStatement(SQL_BALANCE_REPORT_1);
            ps.executeUpdate();
            ps.close();
            logger.debug("#getBalanceReport sql = " + SQL_BALANCE_REPORT_2);
            ps = connection.prepareStatement(SQL_BALANCE_REPORT_2);
            ps.executeUpdate();
            ps.close();
            logger.debug("#getBalanceReport sql = " + SQL_BALANCE_REPORT_3);
            ps = connection.prepareStatement(SQL_BALANCE_REPORT_3);
            ps.executeUpdate();
            ps.close();
            logger.debug("#getBalanceReport sql = " + SQL_BALANCE_REPORT_4);
            ps = connection.prepareStatement(SQL_BALANCE_REPORT_4);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){

                Integer id = rs.getInt("ID");
                String supplierLabel = rs.getString("SUPPLIER_LABEL");
                String plantation = rs.getString("PLANTATION");
                String name = rs.getString("NAME");
                String nameRU = rs.getString("NAME_RU");
                Integer length = rs.getInt("LENGTH");
                Date deliveryDate = null;
                if(rs.getString("INCOMING_DATE")!= null){
                    deliveryDate =  new Date(rs.getTimestamp("INCOMING_DATE").getTime());
                }
                Integer quantity = rs.getInt("QUANTITY");
                Double price = rs.getDouble("PRICE");
                Double priceRUR = rs.getDouble("PRICE_RUR");
                Integer balance = rs.getInt("BALANCE");
                String currency = rs.getString("CURRENCY");

                items.add( new ItemDataImpl(id, supplierLabel,name,nameRU,length, deliveryDate, quantity, price, plantation, currency, priceRUR, balance));

            }
            ps.close();
            ps = connection.prepareStatement(SQL_BALANCE_REPORT_0);
            ps.executeUpdate();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Полчить список заказов не получилось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#getBalanceReport exit");
        return items;
    }

    public List<ItemWriteOffData> listItemWriteOfDataByDelivery(Integer deliveryId) throws Exception{
        logger.debug("#listItemWriteOfDataByDelivery exit");

        List<ItemWriteOffData> dataList = new ArrayList<ItemWriteOffData>();
        Connection connection = null;

        try{
            connection = DBHelper.getConnection();
            PreparedStatement ps = null;
            ps = connection.prepareStatement(SQL_LIST_ITEM_WRITE_OFF);
            ps.setInt(1, deliveryId);
            ResultSet rs = ps.executeQuery();
            Integer prevId = null;
            ItemWriteOffData  data = null;
            while(rs.next()){

                Integer id = rs.getInt("ITEM_ID");
                String supplierLabel = rs.getString("SUPPLIER_LABEL");
                String name = rs.getString("NAME");
                Integer balance = rs.getInt("BALANCE");
                Integer deliveryQtty = rs.getInt("QUANTITY");
                Integer orderId =  rs.getInt("ORDER_ID");
                Integer orderQtty =  rs.getInt("ORDER_QUANTITY");

                if(!id.equals(prevId)){
                    if(prevId!=null) {
                        dataList.add(data);
                    }
                    data = new ItemWriteOffData(id, name, supplierLabel, deliveryQtty, balance);
                    prevId = id;
                }
                data.addOrder(orderId,orderQtty);

            }
            if(data != null) {
                dataList.add(data);
            }
            
            rs.close();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex);
            throw new Exception("Полчить список позиций склада, для проверки на удаление не удалось. Ошибка : " + ex.getMessage());
        }finally{
            DBHelper.closeConnection(connection);
        }
        logger.debug("#getListItemWriteOfDataByDelivery exit");
        return dataList;
    }

}